# -*-coding:utf-8-*-
# 作者    ：SunDuWei
# 创作时间 ：2020-03-02
import requests
import re
import pandas as pd
from progressbar import *
import os
import tushare as ts
import tushare as ts
pro = ts.pro_api('eee65f7f7ff31b668243f94fdf62b04e80cb1a1808f8c42180287ee7')


def future_now_price(date, savepath):
    url = r'http://www.100ppi.com/sf/day-{}.html'.format(date)
    html = requests.get(url)
    text = html.text
    df = pd.DataFrame()

    col = ['商品', '现货价格', '近月代码', '近月价格', '基差1_1', '基差1_2', '主力代码',
           '主力价格', '基差2_1', '基差2_2']
    # 正则表达式
    model = '<tr  align="center"  bgcolor="#fafdff">.*?target=_blank>(.*?)</a>' \
            + '.*?<td>(.*?)</td>' \
            + '.*?<td>(.*?)</td>' \
            + '.*?<td>(.*?)</td>' \
            + '.*?<font color=.*?>(.*?)</font>' \
            + '.*?<font color=.*?>(.*?)</font>' \
            + '.*?<td>(.*?)</td>' \
            + '.*?<td>(.*?)</td>' \
            + '.*?<font color=.*?>(.*?)</font>' \
            + '.*?<font color=.*?>(.*?)</font>'

    pattern = re.compile(model, re.S)
    markets = ['上海期货交易所', '郑州商品交易所', '大连商品交易所']
    mktlist = []
    for mkt in markets:
        mktlist.append(text.find(mkt))

    for i in range(3):
        if i == 2:
            end = len(text)
        else:
            end = mktlist[i + 1]

        start = mktlist[i]
        dataone = re.findall(pattern, text[start:end])
        dfone = pd.DataFrame(dataone, columns=col)
        dfone['交易所'] = markets[i]
        df = df.append(dfone)

        # 删除"&nbsp;"
        for k in ['现货价格', '近月代码', '近月价格', '主力代码', '主力价格']:
            df[k] = df[k].apply(lambda x: x.replace("&nbsp;", ''))
        df.to_excel(savepath, index=False)


def future_now_kind_price(cn_name, savepath, data_date_path):

    # 设置进度条
    widgets = ['进度: ', Percentage(), ' ', Bar('='), ' ', Timer(),
               ' ', ETA(), ' ', FileTransferSpeed()]
    pbar = ProgressBar(widgets=widgets, maxval=len(os.listdir(data_date_path))).start()
    count = 0
    df_kind = pd.DataFrame()
    for filename in os.listdir(data_date_path):
        count += 1
        filepath = data_date_path + r'\%s' % filename
        df = pd.read_excel(filepath)
        df_one = df[df['商品'] == cn_name]
        df_one.rename(columns={'商品': '日期'}, inplace=True)
        df_one['日期'] = filename.replace('.xlsx', ' ')
        df_kind = df_kind.append(df_one)
        pbar.update(count)
    pbar.finish()
    df_kind.to_excel(savepath, index=False)


def commodity_index(plate):
    df = pro.index_daily(ts_code=plate, start_date='20170101', end_date='20191231')
    df['trade_date'] = df['trade_date'].apply(lambda x: x[0:4] + '-' + x[4:6] + '-' + x[6:8])
    return df


if __name__ == '__main__':
    # kind = 'HC'
    # df = pd.read_excel(r'E:\课堂资料\future_data\%s_all_day_data.xlsx' % kind)
    # # 设置进度条
    # widgets = ['进度: ', Percentage(), ' ', Bar('='), ' ', Timer(),
    #            ' ', ETA(), ' ', FileTransferSpeed()]
    # pbar = ProgressBar(widgets=widgets, maxval=len(df['date'])).start()
    # count = 0
    # for date in df['date']:
    #     count += 1
    #     sdate = date.strftime('%Y-%m-%d')
    #     savepath = r'E:\课堂资料\future_data\现期表\%s.xlsx' % sdate
    #     future_now_price(sdate, savepath)
    #     pbar.update(count)
    # pbar.finish()

    kind = 'P'
    cn_name = '棕榈油'
    savepath = r'E:\课堂资料\future_data\品种_现期表\%s.xlsx' % kind
    data_date_path = r'E:\课堂资料\future_data\现期表'
    future_now_kind_price(cn_name, savepath, data_date_path)


    # plate = 'NHMI.NH'
    # cn_plate = '金属'
    # df = commodity_index(plate)
    # df.to_excel(r'E:\课堂资料\future_data\板块指数\index_%s.xlsx' % cn_plate, index=False)
    # plate = 'NHAI.NH'
    # cn_plate = '农业'
    # df = commodity_index(plate)
    # df.to_excel(r'E:\课堂资料\future_data\板块指数\index_%s.xlsx' % cn_plate, index=False)





